{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "unable-findings",
   "metadata": {},
   "source": [
    "# Example 1 - Plot sample_rms for a target over time\n",
    "The intent of this series of Jupyter Notebooks is to demonstrate how metrics can be retrieved from an ISPAQ sqlite database and provide some ideas on how to use or plot those metrics.  \n",
    "\n",
    "This example plots a timeseries of a single metric over time.\n",
    "\n",
    "To run the example, it requires that there are sample_rms values\n",
    "for 2020-10-01 through 2020-10-15 for IU.ANMO.00.BH1.M in a\n",
    "database located at ../ispaq_example.db. To generate these values, you can run:\n",
    "\n",
    "    python3 run_ispaq.py -M sample_rms -S ANMO --starttime 2020-10-01 --endtime 2020-10-16 --output db --db_name ispaq_example.db\n",
    "This example will assume that the above command has already been run and the metrics already exist (it will take several minutes to run).\n",
    "\n",
    "\n",
    "To begin, we need to import the necessary modules:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "alive-fault",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.dates import DateFormatter\n",
    "import matplotlib.dates as mdates\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "built-shape",
   "metadata": {},
   "source": [
    "Now we need to set some variables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fuzzy-underwear",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_name = '../ispaq_example.db'\n",
    "metric = 'sample_rms'\n",
    "startDate = '2020-10-01'\n",
    "endDate = '2020-10-15'\n",
    "target = 'IU.ANMO.00.BH1.M'\n",
    "filename = f'example1_{target}_{startDate}_{endDate}.png'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "surgical-variable",
   "metadata": {},
   "source": [
    "The first step is to create a query that will be used to retrieve the sample_rms (or whatever metric you are using in the code block above)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "regulated-convenience",
   "metadata": {},
   "outputs": [],
   "source": [
    "SQLcommand = f\"SELECT * FROM {metric} WHERE start >= '{startDate}' \" \\\n",
    "             f\"and start < '{endDate }' and (target like '{target}');\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "particular-scratch",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\\nCommand used to retrieve metrics from the sqlite database:\")\n",
    "print(SQLcommand)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "documentary-terminal",
   "metadata": {},
   "source": [
    "\n",
    "Create a connection to the database and run the query, loading it into a pandas dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "controlling-glasgow",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "   conn = sqlite3.connect(db_name)\n",
    "   DF = pd.read_sql_query(SQLcommand, conn, parse_dates=['start','end'])\n",
    "   conn.close\n",
    "except Exception as e:\n",
    "    print(f\"Unable to connect to or find the {metric} table in the database {db_name}:\\n{e}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sixth-stack",
   "metadata": {},
   "source": [
    "At this point, we have created a query to retrieve the metrics from the SQLite database, used sqlite3 to connect to the database, retreieved the metrics, closed the connection, and then ensured that the start times are in a datetime format for plotting purposes. \n",
    "\n",
    "This is what the dataframe looks like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "brilliant-literacy",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(DF)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "motivated-joseph",
   "metadata": {},
   "source": [
    "For plotting purposes, we will create a new dataframe where each column (only one column in this case) is the metric and the associated values, and the index is the date of that value. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "caroline-contemporary",
   "metadata": {},
   "outputs": [],
   "source": [
    "plotDF = pd.DataFrame()\n",
    "plotDF[metric] = DF['value']\n",
    "plotDF.index=DF['start']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "returning-infrastructure",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(plotDF)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "hybrid-richards",
   "metadata": {},
   "source": [
    "Now we use that dataframe to produce a plot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "hourly-basics",
   "metadata": {},
   "outputs": [],
   "source": [
    "ax = plotDF.plot(style='.', color='k', title=metric)\n",
    "ax.xaxis.set_major_locator(mdates.DayLocator(interval=2))\n",
    "plt.minorticks_off()\n",
    "date_form = DateFormatter(\"%m-%d\")\n",
    "ax.xaxis.set_major_formatter(date_form)\n",
    "plt.gcf().autofmt_xdate()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "adjustable-spoke",
   "metadata": {},
   "source": [
    "And save the plot for later viewing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "western-collector",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.savefig(filename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "chief-listing",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
